﻿using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;


namespace Lab1
{
	public class Backend
	{
		private MySqlConnection _connection; 


		public Backend(string connectionstring)
		{
			_connection = new MySqlConnection(connectionstring);
			_connection.Open();
		}

		/**
		* Creates a new table name is the name of the table and 
		* the dictionary contains all the columns 
		*/
		public void CreateTable(string name, IDictionary<string,string> values)
		{
			string strCommand = string.Format("CREATE TABLE {0} (",name);
			
			foreach(var pair in values)
			{
				strCommand += string.Format("{0} {1},", pair.Key, pair.Value);
			}

			strCommand = strCommand.Substring(0, strCommand.Length - 1);
			strCommand += ")";
			MySqlCommand command = new MySqlCommand(strCommand,_connection);
			command.ExecuteNonQuery();
		}

		public bool InsertData(string name, IDictionary<string, object> values)
		{
			string colNames = "(";
			string colDatas = "(";

			MySqlCommand sqlcommand = _connection.CreateCommand();

			foreach (var pair in values)
			{
				MySqlParameter msp = new MySqlParameter();

				if (pair.Value is byte[]) {  //blob
					msp.MySqlDbType = MySqlDbType.LongBlob;
				}
				else if (pair.Value is string) { //string
					msp.MySqlDbType = MySqlDbType.String;
				}
				else if (pair.Value is int){ //int
					msp.MySqlDbType = MySqlDbType.Int32;
				}
				colNames += pair.Key+",";
				colDatas += "@"+pair.Key+",";

				msp.ParameterName = "@"+pair.Key;
				msp.Value = pair.Value;
				sqlcommand.Parameters.Add(msp);
			}
			
			colDatas = colDatas.Substring(0, colDatas.Length - 1);
			colDatas += ")";
			colNames = colNames.Substring(0, colNames.Length - 1);
			colNames += ")";

			sqlcommand.CommandText = string.Format("INSERT INTO {0} {1} VALUES {2}", name, colNames, colDatas);

			return sqlcommand.ExecuteNonQuery() > 0 ? true : false;
		}

		public DataTable GetData(string query)
		{
			MySqlDataAdapter adapter = new MySqlDataAdapter(query,_connection);
			DataTable table = new DataTable();
			adapter.Fill(table);
			return table;
		}

        public bool DeleteData(string query)
        {
			MySqlCommand command = new MySqlCommand(query,_connection);

            return command.ExecuteNonQuery() > 0 ? true :  false;
        }



    }
}
